package com.fau.hm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Hibernate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.fau.hm.entity.MedicalAdmin;
import com.fau.hm.entity.MedicalCallExhale;
import com.fau.hm.entity.MedicalDailyCallSta;
import com.fau.hm.entity.MedicalDailyOrdersSta;
import com.fau.hm.entity.MedicalDailyTaskSta;
import com.fau.hm.entity.MedicalOrdersStorage;
import com.fau.hm.service.IDaliyStaManagerService;
import com.fau.hm.util.DateUtil;
import com.fau.hm.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class DaliyStaManagerService extends BaseService implements IDaliyStaManagerService {

	@Override
	public List<MedicalDailyOrdersSta> findAllOrdersStatistics(MedicalDailyOrdersSta order) {
		List<MedicalDailyOrdersSta> list = this.findAllOrdersList(order);
		return list;
	}

	@Override
	public Workbook exportOrderStatistics(MedicalDailyOrdersSta order) throws Exception {
		List<MedicalDailyOrdersSta> list = this.findAllOrdersList(order);
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDERS_STATISTICS_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		
		NumberFormat format = NumberFormat.getPercentInstance();
		format.setMinimumFractionDigits(2);
		
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalDailyOrdersSta bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getGroupName());
				currentRow.createCell(2).setCellValue(bean.getUserName());
				currentRow.createCell(3).setCellValue(bean.getName());
//				currentRow.createCell(4).setCellValue(bean.getNewOrder());
//				currentRow.createCell(5).setCellValue(bean.getNewOrderMoney().toString());
				currentRow.createCell(6).setCellValue(bean.getComplexNumber());
				currentRow.createCell(7).setCellValue(bean.getComplexMoney().toString());
				currentRow.createCell(8).setCellValue(format.format(bean.getComplexRate()));
				currentRow.createCell(9).setCellValue(bean.getTotalOrdersNumber());
				currentRow.createCell(10).setCellValue(bean.getTotalMoney().toString());
				currentRow.createCell(11).setCellValue(bean.getDeliveryNumber());
				currentRow.createCell(12).setCellValue(bean.getBackNumber());
				currentRow.createCell(13).setCellValue(bean.getBackMoney().toString());
				currentRow.createCell(14).setCellValue(format.format(bean.getBackRate()));
				currentRow.createCell(15).setCellValue(bean.getSignNumber());
				currentRow.createCell(16).setCellValue(bean.getSignMoney().toString());
				currentRow.createCell(17).setCellValue(bean.getNuclearNumber());
				currentRow.createCell(18).setCellValue(bean.getNuclearMoney().toString());
				currentRow.createCell(19).setCellValue(bean.getCancelNumber());
			}
		}
		return workbook;
	}

	/**
	 * @desc 封装前台订单统计展示数据
	 * @param order
	 * @return
	 * @author dangjh
	 * @datetime 2014年11月29日 上午1:32:04
	 */
	@SuppressWarnings("unchecked")
	private List<MedicalDailyOrdersSta> findAllOrdersList(MedicalDailyOrdersSta order) {
		
		List<MedicalDailyOrdersSta> OrdersStaList = new ArrayList<MedicalDailyOrdersSta>();
		
		List<MedicalAdmin> adminList = this.iBaseDao.getBySql("from MedicalAdmin a where a.deptType=? and a.jobStatus=? ", null, SystemConfig.CM_SYSTEM_TYPE, SystemConfig.JOB_WORK_STATUS);
		for(MedicalAdmin admin : adminList) {
			MedicalDailyOrdersSta ordersSta = new MedicalDailyOrdersSta();
			ordersSta.setPlatformName(admin.getGroup().getPlatform().getName());
			ordersSta.setGroupName(admin.getGroup().getName());
			ordersSta.setName(admin.getName());
			ordersSta.setUserName(admin.getUsername());
			
			StringBuilder hql1 = new StringBuilder("from MedicalOrdersStorage a where a.cusSerId=? and a.createTime between ? and ? ");
			
			Date startTime = null;
			Date endTime = null;
			if(null != order.getStartTime() && null != order.getEndTime()) {
				startTime = order.getStartTime();
				endTime = order.getEndTime();
			} else {
				startTime = DateUtil.getDates(true, 0);
				endTime = DateUtil.getDates(false, 0);
			}
			List<MedicalOrdersStorage> ordersList = this.iBaseDao.getBySql(hql1.toString(), null, admin.getId(), startTime, endTime);
			if(ordersList.size() == 0) continue;
			// 总订单数
			ordersSta.setTotalOrdersNumber(ordersList.size());
			// 总订单金额
			BigDecimal totalMoney = new BigDecimal(0);
			
			// 复购数、复购金额
			int complexNumber = 0;
			BigDecimal complexMoney = new BigDecimal(0);
			// 发货单数
			int deliveryNumber = 0;
			// 退单数、退单金额
			int backNumber = 0;
			BigDecimal backMoney = new BigDecimal(0);
			// 签收单数、签收金额
			int signNumber = 0;
			BigDecimal signMoney = new BigDecimal(0);
			// 核过单数、核过金额
			int nuclearNumber = 0;
			BigDecimal nuclearMoney = new BigDecimal(0);
			// 取消单数
			int cancelNumber = 0;
			
			for(MedicalOrdersStorage orders : ordersList) {
				// 新下单数、新下单金额
				// 总金额
				totalMoney = totalMoney.add(orders.getPayment()).add(orders.getFreight());
				
				// 复购数、复购金额
				if(SystemConfig.ORDER_NATURE_TYPE.equals(orders.getNature())) {
					complexNumber++;
					complexMoney = complexMoney.add(orders.getPayment()).add(orders.getFreight());
				}
				// 发货单数
				if(SystemConfig.HAS_DELIVERY.equals(orders.getWarehouseStatus())) {
					deliveryNumber++;
				}
				// 退单数、退单金额
				if(SystemConfig.HAS_CHARGEBACK.equals(orders.getWarehouseStatus())) {
					backNumber++;
					backMoney = backMoney.add(orders.getPayment()).add(orders.getFreight());
				}
				// 签收单数、签收金额
				if(SystemConfig.HAS_SIGNED.equals(orders.getWarehouseStatus())) {
					signNumber++;
					signMoney = signMoney.add(orders.getPayment()).add(orders.getFreight());
				}
				// 核过单数、核过金额
				if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(orders.getOrdersStatus()) && SystemConfig.BE_DELIVERY.equals(orders.getWarehouseStatus())) {
					nuclearNumber++;
					nuclearMoney = nuclearMoney.add(orders.getPayment()).add(orders.getFreight());
				}
				// 取消单数
				if(SystemConfig.ORDER_CANCEL.equals(orders.getOrdersStatus())) {
					cancelNumber++;
				}
			}
			ordersSta.setTotalMoney(totalMoney);
			ordersSta.setComplexNumber(complexNumber);
			ordersSta.setComplexMoney(complexMoney);
			ordersSta.setDeliveryNumber(deliveryNumber);
			ordersSta.setBackNumber(backNumber);
			ordersSta.setBackMoney(backMoney);
			ordersSta.setSignNumber(signNumber);
			ordersSta.setSignMoney(signMoney);
			ordersSta.setNuclearNumber(nuclearNumber);
			ordersSta.setNuclearMoney(nuclearMoney);
			ordersSta.setCancelNumber(cancelNumber);
			
			// 复购率 = 复购单数  / 签收单数
			if(0 != signNumber) {
				double complexRate = (double)complexNumber / (double)signNumber;
				ordersSta.setComplexRate(complexRate);
			} else {
				ordersSta.setComplexRate(0.00);
			}
			
			// 退单率  = 退单金额/(签收金额+退单金额)
			BigDecimal money = signMoney.add(backMoney);
			if(0 != money.compareTo(BigDecimal.ZERO)) {
				BigDecimal backRate = backMoney.divide(money, 2, BigDecimal.ROUND_HALF_UP);
				ordersSta.setBackRate(backRate.doubleValue());
			} else {
				ordersSta.setBackRate(0.00);
			}
			OrdersStaList.add(ordersSta);
		}
		return OrdersStaList;
	}

	@Override
	public List<MedicalDailyCallSta> findAllCallsStatistics(MedicalDailyCallSta call) {
		List<MedicalDailyCallSta> list = this.findAllCallSta(call);
		return list;
	}

	@Override
	public Workbook exportCallStatistics(MedicalDailyCallSta call) throws Exception {
		List<MedicalDailyCallSta> list = this.findAllCallSta(call);
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.CALL_STATISTICS_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalDailyCallSta bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getGroupName());
				currentRow.createCell(2).setCellValue(bean.getUserName());
				currentRow.createCell(3).setCellValue(bean.getName());
				currentRow.createCell(4).setCellValue(bean.getOutGoingCalls());
				currentRow.createCell(5).setCellValue(bean.getOutGoingTime());
				currentRow.createCell(6).setCellValue(bean.getEffectiveCalls());
				if(null != bean.getEffectiveTime()) {
					currentRow.createCell(7).setCellValue(bean.getEffectiveTime());
				}
			}
		}
		return workbook;
	}
	
	/**
	 * @desc 封装前台电话统计展示数据
	 * @param call
	 * @return
	 * @author dangjh
	 * @datetime 2014年11月29日 上午1:38:20
	 */
	@SuppressWarnings("unchecked")
	private List<MedicalDailyCallSta> findAllCallSta(MedicalDailyCallSta call) {
		List<MedicalDailyCallSta> list = new ArrayList<MedicalDailyCallSta>();
		Date startTime = null;
		Date endTime = null;
		if(null != call.getStartTime() && null != call.getEndTime()) {
			startTime = call.getStartTime();
			endTime = call.getEndTime();
		} else {
			startTime = DateUtil.getDates(true, 0);
			endTime = DateUtil.getDates(false, 0);
		}
		List<MedicalCallExhale> callList = this.iBaseDao.getBySql("from MedicalCallExhale m where m.hospitalId = ? and m.outgoingTime between ? and ? "
				+ " group by m.operator ",null, call.getHospitalId(), startTime, endTime);
		for(MedicalCallExhale exhale : callList) {
			MedicalDailyCallSta callSta = new MedicalDailyCallSta();
			MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, exhale.getOperator());
			if(null != admin) {
				if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
					callSta.setGroupName(admin.getGroup().getName());
				}
				callSta.setUserName(admin.getUsername());
				callSta.setName(admin.getName());
				// 呼出数量、呼出时长
				Object object1 = this.iBaseDao.getObjectByHql("select count(m.id), sum(m.callDuration) from MedicalCallExhale m where m.operator = ? and m.outgoingTime between ? and ? ", exhale.getOperator(), startTime, endTime);
				Object[] obj1 = (Object[]) object1;
				callSta.setOutGoingCalls(Integer.parseInt(obj1[0].toString()));
				if(null != obj1[1]) {
					callSta.setOutGoingTime(Integer.parseInt(obj1[1].toString()));
				}
				// 有效呼出数量、有效呼出时长
				Object object2 = this.iBaseDao.getObjectByHql("select count(m.id), sum(m.callDuration) from MedicalCallExhale m where m.operator = ? and m.callDuration > ? and m.outgoingTime between ? and ? ", exhale.getOperator(), 60, startTime, endTime);
				Object[] obj2 = (Object[]) object2;
				callSta.setEffectiveCalls(Integer.parseInt(obj2[0].toString()));
				if(null != obj2[1]) {
					callSta.setEffectiveTime(Integer.parseInt(obj2[1].toString()));
				}
				list.add(callSta);
			}
		}
		return list;
	}

	@Override
	public List<MedicalDailyTaskSta> findAllTasksStatistics(MedicalDailyTaskSta task) {
		List<MedicalDailyTaskSta> list = this.findAllTasksSta(task);
		return list;
	}

	@Override
	public Workbook exportTaskStatistics(MedicalDailyTaskSta task) throws Exception {
		List<MedicalDailyTaskSta> list = this.findAllTasksSta(task);
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.TASK_STATISTICS_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalDailyTaskSta bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getGroupName());
				currentRow.createCell(2).setCellValue(bean.getUserName());
				currentRow.createCell(3).setCellValue(bean.getName());
				currentRow.createCell(4).setCellValue(bean.getCallTasks());
				currentRow.createCell(5).setCellValue(bean.getProcessedCallTasks());
				currentRow.createCell(6).setCellValue(bean.getPendingCallTasks());
				currentRow.createCell(7).setCellValue(bean.getUntreatedCallTasks());
				currentRow.createCell(8).setCellValue(bean.getSingleVolume());
				currentRow.createCell(9).setCellValue(bean.getSingleRate());
			}
		}
		return workbook;
	}
	
	/**
	 * @desc 封装前台任务统计展示数据
	 * @param task
	 * @return List<MedicalDailyTaskSta>
	 * @author dangjh
	 * @datetime 2014年11月29日 上午1:38:20
	 */
	@SuppressWarnings("unchecked")
	private List<MedicalDailyTaskSta> findAllTasksSta(MedicalDailyTaskSta task) {
		List<MedicalDailyTaskSta> list = new ArrayList<MedicalDailyTaskSta>();
		Date startTime = null;
		Date endTime = null;
		if(null != task.getStartTime() && null != task.getEndTime()) {
			startTime = task.getStartTime();
			endTime = task.getEndTime();
		} else {
			startTime = DateUtil.getDates(true, 0);
			endTime = DateUtil.getDates(false, 0);
		}
		List<MedicalAdmin> userList = this.iBaseDao.getBySql("from MedicalAdmin a where a.enterpriseId = ? and a.deptType = ? ", null, task.getHospitalId(), SystemConfig.CM_SYSTEM_TYPE);
		for(MedicalAdmin user : userList) {
			MedicalDailyTaskSta taskSta = new MedicalDailyTaskSta();
			Hibernate.initialize(user.getGroup());
			taskSta.setGroupName(user.getGroup().getName());
			taskSta.setUserName(user.getUsername());
			taskSta.setName(user.getName());
			
			// 已处理任务数
			int processedCallTasks = this.iBaseDao.getIntCount("select count(*) from MedicalCallEnd c where c.handleUserId = ? and c.updateTime between ? and ? ", user.getId(), startTime, endTime);
			taskSta.setProcessedCallTasks(processedCallTasks);
			
			// 待处理任务数
			int pendingCallTasks = this.iBaseDao.getIntCount("select count(*) from MedicalCallPending c where c.handleUserId = ? and c.updateTime between ? and ? ", user.getId(), startTime, endTime);
			taskSta.setPendingCallTasks(pendingCallTasks);
			
			// 未处理任务数
			int untreatedCallTasks = this.iBaseDao.getIntCount("select count(*) from MedicalCallUntreated c where c.handleUserId = ? and c.allocationTime between ? and ? ", user.getId(), startTime, endTime);
			taskSta.setUntreatedCallTasks(untreatedCallTasks);
			
			// 电话任务数
			int callTasks = processedCallTasks + pendingCallTasks + untreatedCallTasks;
			taskSta.setCallTasks(callTasks);
			
			// 出单量
			int singleVolume = this.iBaseDao.getIntCount("select count(*) from MedicalOrdersStorage m where m.cusSerId = ? and m.createTime between ? and ? ", user.getId(), startTime, endTime);
			taskSta.setSingleVolume(singleVolume);
			
			// 出单率
			String singleRate = "";
			if(0 != callTasks) {
				double rate = ((double)singleVolume / (double)callTasks) * 100;
				singleRate = String.valueOf(rate)+"%";
			} else {
				singleRate = "0%";
			}
			taskSta.setSingleRate(singleRate);
			list.add(taskSta);
		}
		return list;
	}
	
}
